package com.task.www.dao;

import com.task.www.po.Food;
import com.task.www.po.Merchant;
import com.task.www.util.JDBCUtilsMc;
import com.task.www.util.JDBCUtilsUs;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class MechantData {

    private Connection conn = null;
    private String sql = null;
    private PreparedStatement prep = null;
    ResultSet rs = null;

    public void insertaccout( String accout, String password){
        try {
            conn = JDBCUtilsMc.getConnection();
            sql = "insert into register values(null,?,?,null,null,0,0,0)";
            prep = conn.prepareStatement(sql);
            prep.setString(1,accout);
            prep.setString(2,password);
            prep.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtilsMc.close(prep,conn);
        }
    }

    public boolean selectaccout(String accout ,String password){
        try {
            conn = JDBCUtilsMc.getConnection();
            sql = "select accout password from register where accout = ? and password = ?";
            prep = conn.prepareStatement(sql);
            prep.setString(1,accout);
            prep.setString(2,password);
            rs = prep.executeQuery();
            return rs.next();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtilsMc.close(prep,conn,rs);
        }
        return false;
    }

    public Merchant getaccout(String accout){
        try {
            conn = JDBCUtilsMc.getConnection();
            sql = "select * from register where accout = ?";
            prep = conn.prepareStatement(sql);
            prep.setString(1,accout);
            rs = prep.executeQuery();
            if(rs.next()){
            String name = rs.getNString("restaurant_name");
            String introduce = rs.getNString("restaurant_introduce");
            int monney = rs.getInt("monney");
            int wait = rs.getInt("wait");
            int already = rs.getInt("already");
            return new Merchant(0,accout,"",name,introduce,monney,wait,already);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtilsMc.close(prep,conn,rs);
        }
        return null;
    }

    public ArrayList<String> getmenuSimple(String accout ){
        ArrayList<String> arrayList = new ArrayList<>();
        try {
            conn = JDBCUtilsMc.getConnection();
            sql = "select food.name from food,register where register.ids=food.fkid and register.accout = ?";
            prep = conn.prepareStatement(sql);
            prep.setString(1,accout);
            rs = prep.executeQuery();
            String name = null;
            while(rs.next()){
                name = rs.getString(1);
                arrayList.add(name);
            }
            return arrayList;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtilsMc.close(prep,conn,rs);
        }
        return null;
    }

    public ArrayList<Integer> getCoupon(String accout){
        ArrayList<Integer> coupon = new ArrayList<>();
        try {
            conn = JDBCUtilsMc.getConnection();
            sql = "select coupon.up,coupon.down from coupon,register where register.ids=coupon.fkid and register.accout = ?";
            prep = conn.prepareStatement(sql);
            prep.setString(1,accout);
            rs = prep.executeQuery();
            while(rs.next()){
                coupon.add(rs.getInt(1));
                coupon.add(rs.getInt(2));
            }
            return coupon;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtilsMc.close(prep,conn,rs);
        }
        return null;
    }

    public void updatename(String accout,String name){
        try {
            conn = JDBCUtilsMc.getConnection();
            sql = "update register set restaurant_name = ? where accout = ?";
            prep = conn.prepareStatement(sql);
            prep.setString(1,name);
            prep.setString(2,accout);
            prep.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtilsMc.close(prep,conn);
        }
    }

    public void updateIntroduce(String accout,String introduce){
        try {
            conn = JDBCUtilsMc.getConnection();
            sql = "update register set restaurant_introduce = ? where accout = ?";
            prep = conn.prepareStatement(sql);
            prep.setString(1,introduce);
            prep.setString(2,accout);
            prep.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtilsMc.close(prep,conn);
        }
    }

    public ArrayList<Food> selectMenu(String accout){
        try {
            conn = JDBCUtilsMc.getConnection();
            sql = "select food.name,food.price,food.introduce from food,register where food.fkid=register.ids and register.accout=?";
            prep = conn.prepareStatement(sql);
            prep.setString(1,accout);
            rs = prep.executeQuery();
            ArrayList<Food> foods = new ArrayList<>();
            while(rs.next()){
                foods.add(new Food(rs.getString(1),rs.getInt(2), rs.getString(3)));
            }
            return foods;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtilsMc.close(prep,conn,rs);
        }
        return null;
    }

    public ArrayList<String> selectFood(String accout,String name){
        //先获取菜名，价格，介绍 ,自己的评论
        ArrayList<String> food = new ArrayList<>();
        try {
            conn = JDBCUtilsMc.getConnection();
            sql = "select food.name,food.price,food.introduce,food.comment from food,register where food.fkid=register.ids and register.accout=? and food.name = ? ";
            prep = conn.prepareStatement(sql);
            prep.setString(1,accout);
            prep.setString(2,name);
            rs = prep.executeQuery();
            while(rs.next()){
                food.add(rs.getString(1));
                food.add(String.valueOf(rs.getInt(2)));
                food.add(rs.getString(3));
                food.add(rs.getString(4));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtilsMc.close(prep,conn,rs);
        }

        //接着获取客户的评价
        try {
            conn = JDBCUtilsMc.getConnection();
            sql =   "select t5.accout,t4.comments from " +
                    "(select t1.comments,t1.fkid_user from comment t1,food t2,register t3 " +
                    "where t1.fkid_food=t2.id and t2.fkid=t3.ids " +
                    "and t3.accout=? and t2.`name`=?) t4 left join user.register t5 on t4.fkid_user=t5.ids;";
            prep = conn.prepareStatement(sql);
            prep.setString(1,accout);
            prep.setString(2,name);
            rs = prep.executeQuery();
            while(rs.next()){
                food.add(rs.getString(1));
                food.add(rs.getString(2));
            }
            return food;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtilsMc.close(prep,conn,rs);
        }

        return null;
    }

    //重载通过id获取评论
    public ArrayList<String> selectFood(int ids,String name){
        //先获取菜名，价格，介绍 ,自己的评论
        ArrayList<String> food = new ArrayList<>();
        try {
            conn = JDBCUtilsMc.getConnection();
            sql = "select food.name,food.price,food.introduce,food.comment from food,register where food.fkid=register.ids and register.ids=? and food.name = ? ";
            prep = conn.prepareStatement(sql);
            prep.setInt(1,ids);
            prep.setString(2,name);
            rs = prep.executeQuery();
            while(rs.next()){
                food.add(rs.getString(1));
                food.add(String.valueOf(rs.getInt(2)));
                food.add(rs.getString(3));
                food.add(rs.getString(4));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtilsMc.close(prep,conn,rs);
        }

        //接着获取客户的评价
        try {
            conn = JDBCUtilsMc.getConnection();
            sql =   "select t5.accout,t4.comments from " +
                    "(select t1.comments,t1.fkid_user from comment t1,food t2,register t3 " +
                    "where t1.fkid_food=t2.id and t2.fkid=t3.ids " +
                    "and t3.ids=? and t2.`name`=?) t4 left join user.register t5 on t4.fkid_user=t5.ids;";
            prep = conn.prepareStatement(sql);
            prep.setInt(1,ids);
            prep.setString(2,name);
            rs = prep.executeQuery();
            while(rs.next()){
                food.add(rs.getString(1));
                food.add(rs.getString(2));
            }
            return food;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtilsMc.close(prep,conn,rs);
        }

        return null;
    }

    //更改菜的信息
    public void updateFood(int ids,String name,String str,int i){
        try {
            conn = JDBCUtilsMc.getConnection();
            if(i==1){
                sql = "update food set name = ? where name = ? and fkid=?";
            }
            if(i==2){
                sql = "update food set price = ? where name = ? and fkid=?";
            }
            if(i==3){
                sql = "update food set introduce = ? where name = ? and fkid=?";
            }
            if(i==4){
                sql = "update food set comment = ? where name = ? and fkid=?";
            }
            prep = conn.prepareStatement(sql);

            if(i==2){
                //价格的格式
                prep.setInt(1,Integer.parseInt(str));
            }else {
                prep.setString(1, str);
            }
            prep.setString(2,name);
            prep.setInt(3,ids);
            prep.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtilsMc.close(prep,conn);
        }
    }

    //获取商家id
    public int  getMechId(String accout){
        try {
            conn = JDBCUtilsMc.getConnection();
            sql = " select ids from register where accout = ?";
            prep = conn.prepareStatement(sql);
            prep.setString(1,accout);
            rs = prep.executeQuery();
            int i = 0;
           if(rs.next()) {
               i = rs.getInt(1);
           }
           return i;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        finally {
            JDBCUtilsMc.close(prep,conn,rs);
        }
        return 0;
    }

//    增删改优惠券
    public void dmlcoupon(int up,int down,int fkid,int i){
        try {
            conn = JDBCUtilsMc.getConnection();
            if(i==1) {
                sql = "insert into coupon values(null,?,?,?)";
            }
            if(i==2){
                sql = "delete from coupon where up = ? and down = ? and fkid = ?";
            }
            prep = conn.prepareStatement(sql);
            prep.setInt(1,up);
            prep.setInt(2,down);
            prep.setInt(3,fkid);
            prep.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtilsMc.close(prep,conn);
        }
    }
//重载增删改优惠券
    public void dmlcoupon(int up,int down,int fkid,int i,int newup,int newdown){
        try {
            conn = JDBCUtilsMc.getConnection();
            sql = "update coupon set up = ? ,down = ? where up = ? and down = ? and fkid = ?";
            prep = conn.prepareStatement(sql);
            prep.setInt(1,newup);
            prep.setInt(2,newdown);
            prep.setInt(3,up);
            prep.setInt(4,down);
            prep.setInt(5,fkid);
            prep.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtilsMc.close(prep,conn);
        }
    }

    //获取订单
    public ArrayList<String> getbuy(int fkidm){
        try {
            conn = JDBCUtilsUs.getConnection();
            sql = " select boolean,name from buy where fkidm = ?";
            prep = conn.prepareStatement(sql);
            prep.setInt(1,fkidm);
            rs = prep.executeQuery();
            ArrayList<String> buy = new ArrayList<>();
            while(rs.next()) {
                if(rs.getBoolean(1)==false){
                    buy.add(rs.getString(2));
                }
            }
            return buy;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        finally {
            JDBCUtilsMc.close(prep,conn,rs);
        }
        return null;
    }

    //接受订单
    public void deletebuy(int fkidm,String name){
        ArrayList<Integer> fkidu = new ArrayList<>();
        int[] waitalready = {0,0};
        try {
            conn = JDBCUtilsUs.getConnection();
            sql = "select fkidu from buy where fkidm = ? and name = ?";
            prep = conn.prepareStatement(sql);
            prep.setInt(1,fkidm);
            prep.setString(2,name);
            rs = prep.executeQuery();
            while(rs.next()){
                fkidu.add(rs.getInt(1));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtilsUs.close(prep,conn,rs);
        }

        try {
            conn = JDBCUtilsUs.getConnection();
            sql = "delete from buy where fkidu = ? and fkidm = ? and name = ?";
            prep = conn.prepareStatement(sql);
            prep.setInt(1,fkidu.get(0));
            prep.setInt(2,fkidm);
            prep.setString(3,name);
            prep.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtilsMc.close(prep,conn);
        }

        try {
            conn = JDBCUtilsMc.getConnection();
            sql = "select wait,already from register where ids = ? ";
            prep = conn.prepareStatement(sql);
            prep.setInt(1,fkidm);
            rs = prep.executeQuery();
            while(rs.next()){
                waitalready[0] = rs.getInt(1);
                waitalready[1] = rs.getInt(2);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtilsUs.close(prep,conn,rs);
        }

        try {
            conn = JDBCUtilsMc.getConnection();
            sql = "update register set wait = ?,already = ?  where ids = ? ";
            prep = conn.prepareStatement(sql);
            prep.setInt(1,waitalready[0]-1);
            prep.setInt(2,waitalready[1]+1);
            prep.setInt(3,fkidm);
           prep.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtilsUs.close(prep,conn);
        }
    }

}

